﻿CREATE TABLE [dbo].[Applicant] (
    [ID]                                                  UNIQUEIDENTIFIER CONSTRAINT [DF_AdoptionApplications_ID] DEFAULT (newid()) ROWGUIDCOL NOT NULL,
    [PersonID]                                            UNIQUEIDENTIFIER NOT NULL,
    [ApplicantVeterinariansID]                            UNIQUEIDENTIFIER NULL,
    [ApplicantType]                                       NVARCHAR (1)     CONSTRAINT [DF_Applicant_ApplicantType] DEFAULT ('A') NOT NULL,
    [DateSubmitted]                                       DATETIME2 (7)    NOT NULL,
    [AppNameFirst]                                        NVARCHAR (50)    NOT NULL,
    [AppNameLast]                                         NVARCHAR (50)    NOT NULL,
    [AppSpouseNameFirst]                                  NVARCHAR (50)    NULL,
    [AppSpouseNameLast]                                   NVARCHAR (50)    NULL,
    [AppCellPhone]                                        NVARCHAR (15)    NULL,
    [AppHomePhone]                                        NVARCHAR (15)    NULL,
    [AppAddressStreet1]                                   NVARCHAR (50)    NOT NULL,
    [AppAddressCity]                                      NVARCHAR (50)    NOT NULL,
    [AppAddressStateId]                                   NVARCHAR (2)     NULL,
    [AppAddressZIP]                                       NVARCHAR (10)    NOT NULL,
    [AppEmail]                                            NVARCHAR (200)   NULL,
    [AppDateBirth]                                        DATETIME2 (7)    NULL,
    [AppEmployer]                                         NVARCHAR (50)    NULL,
    [ResidenceOwnershipID]                                INT              NOT NULL,
    [ResidenceTypeID]                                     INT              NOT NULL,
    [ResidenceIsPetAllowed]                               BIT              NULL,
    [ResidenceIsPetDepositRequired]                       BIT              NULL,
    [ResidencePetDepositAmount]                           DECIMAL (18, 2)  NULL,
    [ResidenceIsPetDepositPaid]                           BIT              NULL,
    [ResidencePetDepositCoverageID]                       INT              NULL,
    [ResidenceIsPetSizeWeightLimit]                       BIT              NULL,
    [ResidenceLandlordName]                               NVARCHAR (100)   NULL,
    [ResidenceLandlordNumber]                             NVARCHAR (14)    NULL,
    [ResidenceLengthOfResidence]                          NVARCHAR (30)    NOT NULL,
    [IsAppOrSpouseStudent]                                BIT              NOT NULL,
    [StudentTypeID]                                       INT              NULL,
    [IsAppTravelFrequent]                                 BIT              NOT NULL,
    [AppTravelFrequency]                                  NVARCHAR (50)    NULL,
    [WhatIfTravelPetPlacement]                            NVARCHAR (4000)  NULL,
    [WhatIfMovingPetPlacement]                            NVARCHAR (4000)  NULL,
    [ResidenceNumberOccupants]                            NVARCHAR (50)    NULL,
    [ResidenceAgesOfChildren]                             NVARCHAR (200)   NULL,
    [ResidenceIsYardFenced]                               BIT              NOT NULL,
    [ResidenceFenceType]                                  NVARCHAR (50)    NULL,
    [ResidenceFenceHeight]                                NVARCHAR (50)    NULL,
    [LengthPetLeftAloneHoursInDay]                        NVARCHAR (20)    NULL,
    [LengthPetLeftAloneDaysOfWeek]                        NVARCHAR (20)    NULL,
    [PetKeptLocationInOutDoors]                           NVARCHAR (200)   NULL,
    [IsPetKeptLocationInOutDoorsTotallyInside]            BIT              NOT NULL,
    [IsPetKeptLocationInOutDoorsMostlyInside]             BIT              NOT NULL,
    [IsPetKeptLocationInOutDoorsTotallyOutside]           BIT              NOT NULL,
    [IsPetKeptLocationInOutDoorMostlyOutsides]            BIT              NOT NULL,
    [PetKeptLocationInOutDoorsExplain]                    NVARCHAR (4000)  NULL,
    [PetLeftAloneHours]                                   NVARCHAR (20)    NULL,
    [PetLeftAloneDays]                                    NVARCHAR (20)    NULL,
    [PetKeptLocationAloneRestriction]                     NVARCHAR (MAX)   NULL,
    [IsPetKeptLocationAloneRestrictionLooseIndoors]       BIT              NOT NULL,
    [IsPetKeptLocationAloneRestrictionGarage]             BIT              NOT NULL,
    [IsPetKeptLocationAloneRestrictionOutsideKennel]      BIT              NOT NULL,
    [IsPetKeptLocationAloneRestrictionCratedIndoors]      BIT              NOT NULL,
    [IsPetKeptLocationAloneRestrictionCratedOutdoors]     BIT              NOT NULL,
    [IsPetKeptLocationAloneRestrictionLooseInBackyard]    BIT              NOT NULL,
    [IsPetKeptLocationAloneRestrictionTiedUpOutdoors]     BIT              NOT NULL,
    [IsPetKeptLocationAloneRestrictionBasement]           BIT              NOT NULL,
    [IsPetKeptLocationAloneRestrictionOther]              BIT              NOT NULL,
    [PetKeptLocationAloneRestrictionExplain]              NVARCHAR (4000)  NULL,
    [PetKeptLocationSleepingRestriction]                  NVARCHAR (MAX)   NULL,
    [IsPetKeptLocationSleepingRestrictionLooseIndoors]    BIT              NOT NULL,
    [IsPetKeptLocationSleepingRestrictionGarage]          BIT              NOT NULL,
    [IsPetKeptLocationSleepingRestrictionOutsideKennel]   BIT              NOT NULL,
    [IsPetKeptLocationSleepingRestrictionCratedIndoors]   BIT              NOT NULL,
    [IsPetKeptLocationSleepingRestrictionCratedOutdoors]  BIT              NOT NULL,
    [IsPetKeptLocationSleepingRestrictionLooseInBackyard] BIT              NOT NULL,
    [IsPetKeptLocationSleepingRestrictionTiedUpOutdoors]  BIT              NOT NULL,
    [IsPetKeptLocationSleepingRestrictionBasement]        BIT              NOT NULL,
    [IsPetKeptLocationSleepingRestrictionInBedOwner]      BIT              NOT NULL,
    [IsPetKeptLocationSleepingRestrictionOther]           BIT              NOT NULL,
    [PetKeptLocationSleepingRestrictionExplain]           NVARCHAR (4000)  NULL,
    [PetAdoptionReason]                                   NVARCHAR (4000)  NULL,
    [IsPetAdoptionReasonHousePet]                         BIT              NOT NULL,
    [IsPetAdoptionReasonGuardDog]                         BIT              NOT NULL,
    [IsPetAdoptionReasonWatchDog]                         BIT              NOT NULL,
    [IsPetAdoptionReasonGift]                             BIT              NOT NULL,
    [IsPetAdoptionReasonCompanionChild]                   BIT              NOT NULL,
    [IsPetAdoptionReasonCompanionPet]                     BIT              NOT NULL,
    [IsPetAdoptionReasonJoggingPartner]                   BIT              NOT NULL,
    [IsPetAdoptionReasonOther]                            BIT              NOT NULL,
    [PetAdoptionReasonExplain]                            NVARCHAR (4000)  NULL,
    [FilterAppHasOwnedHuskyBefore]                        BIT              NOT NULL,
    [FilterAppTraitsDesired]                              NVARCHAR (4000)  NULL,
    [FilterAppIsCatOwner]                                 BIT              NOT NULL,
    [FilterAppCatsOwnedCount]                             NVARCHAR (20)    NULL,
    [FilterAppIsAwareHuskyAttributes]                     BIT              NOT NULL,
    [IsAllAdultsAgreedOnAdoption]                         BIT              NOT NULL,
    [IsAllAdultsAgreedOnAdoptionReason]                   NVARCHAR (4000)  NULL,
    [FilterAppDogsInterestedIn]                           NVARCHAR (4000)  NULL,
    [IsCompleted]                                         BIT              CONSTRAINT [DF__AdoptionA__IsCom__56E8E7AB] DEFAULT ((0)) NULL,
    [IsDeleted]                                           BIT              CONSTRAINT [DF__AdoptionA__IsDel__57DD0BE4] DEFAULT ((0)) NULL,
    CONSTRAINT [PK_AdoptionApplications] PRIMARY KEY CLUSTERED ([ID] ASC, [PersonID] ASC),
    CONSTRAINT [FK_AdoptionApplications_ApplicantVeterinarians] FOREIGN KEY ([ApplicantVeterinariansID]) REFERENCES [dbo].[ApplicantVeterinarians] ([ID]),
    CONSTRAINT [FK_AdoptionApplications_Enum_PetDepositCoverage] FOREIGN KEY ([ResidencePetDepositCoverageID]) REFERENCES [dbo].[Enum_PetDepositCoverage] ([ID]),
    CONSTRAINT [FK_AdoptionApplications_Enum_ResidenceOwnershipType] FOREIGN KEY ([ResidenceOwnershipID]) REFERENCES [dbo].[Enum_ResidenceOwnershipType] ([ID]),
    CONSTRAINT [FK_AdoptionApplications_Enum_ResidenceType] FOREIGN KEY ([ResidenceTypeID]) REFERENCES [dbo].[Enum_ResidenceType] ([ID]),
    CONSTRAINT [FK_AdoptionApplications_Enum_StudentType] FOREIGN KEY ([StudentTypeID]) REFERENCES [dbo].[Enum_StudentType] ([ID]),
    CONSTRAINT [FK_EntityPersonID__AdoptionApplications_PersonID] FOREIGN KEY ([PersonID]) REFERENCES [dbo].[Entity_Person] ([ID])
);

